﻿CREATE PROCEDURE [util].[JobOutputCleanupByDate]
@FileRetentionInDays TINYINT=7, @FilePrefix NVARCHAR (50)=N'Job_Output_'
AS
BEGIN
	DECLARE @RC int, @ERROR int
	SET @RC = 0
	
	IF(@FileRetentionInDays < 1)
		BEGIN
			RAISERROR('Minimum file retention is 1 day',16,1)
			SET @ERROR = @@ERROR
			GOTO ERROR_HANDLER
		END

	IF(@FilePrefix IS NULL)
		SET @FilePrefix = N''

	PRINT 'SQL Agent Job output files prefixed with '
		+ @FilePrefix
		+ ' will be deleted if they are more than '
		+ CAST(@FileRetentionInDays AS varchar) 
		+ ' days old.'

	DECLARE @SQLDataRoot nvarchar(256)
	EXEC @RC = util.GetSQLDataRoot @SQLDataRoot OUTPUT
	IF(@RC <> 0) GOTO ERROR_HANDLER
		
	DECLARE @CmdStatement nvarchar(512)
	SET @CmdStatement = N'FORFILES /P "' 
		+ @SQLDataRoot
		+ N'\LOG" /M "'
		+ @FilePrefix
		+ N'*.txt" /D -'
		+ CAST(@FileRetentionInDays AS nvarchar)
		+ N' /C "cmd /c DEL @FILE"'

	PRINT 'Running CMD:'
		+ CHAR(13)
		+ @CmdStatement

	EXEC master.dbo.xp_cmdshell @CmdStatement--, NO_OUTPUT
	
	GOTO EXIT_PROC

ERROR_HANDLER:
	IF(@RC = 0) SET @RC = @ERROR

EXIT_PROC:
	RETURN(@RC)
END